#coding:utf8
from django.http import HttpResponse
from django.shortcuts import render_to_response
from review.mysqlhandler import dbh
import datetime

def index(request):
	daynow = datetime.datetime.now().strftime('%Y年%m月%d日')
	m = Menu()
	fllist = m.retFL()
	cplist = m.retCP()
	pjlist = m.retPJ()
	pflist = m.retPF()
	djlist = m.retDJ()
	return render_to_response('menu.html',{'daynow':daynow,\
		'fllist':fllist,\
		'cplist':cplist,\
		'pjlist':pjlist,\
		'pflist':pflist,\
		'djlist':djlist})

def pf(request):	#点击一个等级进行评分
	cpid = request.GET.get('cpid',None)
	pfdj = request.GET.get('pfdj',None)
	info = request.GET.get('info',None)
	msg = ''# 响应给前端ajax请求的信息
	try:
		newid = None # 新增评论的编号
		rev = Menu()
		maxid = rev.retMaxID()[0][0] # 评分编号后4位尾号
		if maxid:
			newid = datetime.datetime.now().strftime('%Y%m%d') + str(int(maxid)+1).zfill(4)
		else:
			newid = datetime.datetime.now().strftime('%Y%m%d') + '0001'

		if newid:
			sqlAddPF = '''
				INSERT INTO T_PF VALUES('%s',NOW(),'%s','%s','%s');
			''' % (newid,cpid,pfdj,info)
			dbh.ExecNonQuery(sqlAddPF)
	except:
		msg = 'failed'

	return HttpResponse(msg)

class Menu(object):
	"""docstring for MenuFL"""
	dataNow = datetime.datetime.now().strftime('%Y-%m-%d') # 获取当前日期并转为字符型

	def __init__(self):
		pass

	def retFL(self):	#分类
		sqlFL = '''
			SELECT C.FL_ID,C.FLMC FROM T_MENU A
			LEFT JOIN T_CP B ON A.CP_ID = B.CP_ID
			LEFT JOIN T_FL C ON B.FL_ID = C.FL_ID
			WHERE A.RQ = '%s'
			GROUP BY C.FLMC
			ORDER BY B.FL_ID;
		''' % self.dataNow
		return dbh.ExecQuery(sqlFL)
		
	def retCP(self):	#菜品
		sqlCP = '''
			SELECT B.FL_ID,A.CP_ID,B.CPMC FROM T_MENU A
			LEFT JOIN 
			(
				SELECT A.FL_ID,B.FLMC,A.CP_ID,A.CPMC FROM T_CP A
				LEFT JOIN T_FL B ON A.FL_ID = B.FL_ID
			) B ON A.CP_ID = B.CP_ID
			WHERE A.RQ = '%s'
			ORDER BY A.CP_ID;
		''' % self.dataNow
		return dbh.ExecQuery(sqlCP)
	
	def retPF(self):	#评分
		sqlPF = '''
			SELECT A.CP_ID,A.GRADE_ID,COALESCE(B.PF,0)
			FROM (
				SELECT A.CP_ID,B.GRADE_ID
				FROM T_MENU A 
				LEFT JOIN T_GRADE B ON 1 = 1
				WHERE A.RQ = '%s'
				GROUP BY A.CP_ID,B.GRADE_ID
			) A
			LEFT JOIN (
				SELECT CP_ID,GRADE_ID,COUNT(*) AS PF
				FROM T_PF
				WHERE DATE_FORMAT(RQ,'%%Y-%%m-%%d') = '%s'
				GROUP BY CP_ID,GRADE_ID
			) B ON A.CP_ID = B.CP_ID AND A.GRADE_ID = B.GRADE_ID
			GROUP BY A.CP_ID,A.GRADE_ID
			ORDER BY A.CP_ID;
		''' % (self.dataNow,self.dataNow)
		return dbh.ExecQuery(sqlPF)

	def retPJ(self):	#评论
		sqlPJ = '''
			SELECT CP_ID,COUNT(REVIEW) FROM T_RATE
			WHERE DATE_FORMAT(RQ,'%%Y-%%m-%%d') = '%s'
			GROUP BY CP_ID;
		''' % self.dataNow
		return dbh.ExecQuery(sqlPJ)

	def retDJ(self):	#3个等级
		sqlDJ = '''
			SELECT GRADE_ID,GRADE_NAME 
			FROM T_GRADE
			ORDER BY GRADE_ID;
		'''
		return dbh.ExecQuery(sqlDJ)

	def retMaxID(self):	#当日最后一个评分编号数字部分
		sqlMaxID = '''
			SELECT RIGHT(MAX(PF_ID),4) FROM T_PF
			WHERE DATE_FORMAT(RQ,'%%Y-%%m-%%d') = '%s';
		''' % self.dataNow
		return dbh.ExecQuery(sqlMaxID)